import os
import threading
from datetime import datetime, date
from decimal import Decimal

import duckdb
from PySide6.QtCore import Qt
from PySide6.QtGui import QMovie, QStandardItemModel, QStandardItem, QAction
from PySide6.QtWidgets import QMainWindow, QMessageBox, QFileDialog, QApplication, QHeaderView, QMenu
from openpyxl.workbook import Workbook

from config import MAIN_PATH
from utils.check_csv import check_csv_file
from windows.ingest_to_db_dialog import IngestToDBDialog
from windows.load_to_db_dialog import Ui_LoadToRemoteDBDialog, LoadToRemoteDBDialog
from windows.log_dialog import LogDialog
from windows.main_ui import Ui_FuCSVReader
from utils.tigger_method import on_action_order_by_triggered, on_action_choose_column_triggered, on_select_text_changed, \
    on_where_text_changed, on_order_by_text_changed, on_limit_text_changed
from utils.handle_csv import filename_to_table_name, query_data_by_duckdb, load_file_by_duckdb
from utils.signal_emitter import SignalEmitter
from utils.update import UpdateProgress
from windows.copyright_dialog import CopyrightDialog


class MyMainWindow(QMainWindow):
    def __init__(self, app):
        super().__init__()

        self.file_path = None
        self.conn = duckdb.connect(database=':memory:')
        self.file_type = None
        self.duckdb_data = None
        self.model = None
        self.table_name = 'Null'
        self.df = None
        self.download_file_path = os.path.join(os.path.expanduser('~'), 'Downloads')
        self.gif_path = MAIN_PATH.joinpath("loading.gif")
        self.export_file_name = 'Null'

        # 设置界面为我们生成的界面
        self.csv_reader_ui = Ui_FuCSVReader()
        self.csv_reader_ui.setupUi(self)
        self.csv_reader_ui.movie = QMovie(str(self.gif_path))
        self.csv_reader_ui.label.setMovie(self.csv_reader_ui.movie)
        self.csv_reader_ui.movie.start()
        self.csv_reader_ui.label.hide()
        self.csv_reader_ui.textEdit.hide()
        self.csv_reader_ui.textEdit.setReadOnly(True)
        self.csv_reader_ui.progressBar.hide()
        self.csv_reader_ui.progressText.hide()
        self.csv_reader_ui.loadCountLabel.hide()

        self.csv_reader_ui.actionStop.setVisible(False)

        custom_header_view = CustomHeaderView(Qt.Horizontal, self.csv_reader_ui.tableView, self)
        self.csv_reader_ui.tableView.setHorizontalHeader(custom_header_view)
        self.csv_reader_ui.tableView.horizontalHeader().setCascadingSectionResizes(True)
        self.csv_reader_ui.tableView.horizontalHeader().setDefaultSectionSize(120)
        self.csv_reader_ui.sELECTLineEdit.textChanged.connect(lambda text: on_select_text_changed(text, self))
        self.csv_reader_ui.wHERELineEdit.textChanged.connect(lambda text: on_where_text_changed(text, self))
        self.csv_reader_ui.oRDERBYLineEdit.textChanged.connect(lambda text: on_order_by_text_changed(text, self))
        self.csv_reader_ui.lIMITLineEdit.textChanged.connect(lambda text: on_limit_text_changed(text, self))
        # self.csv_reader_ui.tableView.horizontalHeader().setStretchLastSection(True)

        # 设置表头对齐方式为靠右
        horizontal_header = self.csv_reader_ui.tableView.horizontalHeader()
        horizontal_header.setDefaultAlignment(Qt.AlignLeft | Qt.AlignVCenter)

        self.csv_reader_ui.actionOPEN.triggered.connect(lambda: self.start_open_file_thread(None))
        self.csv_reader_ui.actionReload.triggered.connect(
            lambda: self.reload_file(self.csv_reader_ui.filePathLineEdit.text()))
        self.csv_reader_ui.actionDownload.triggered.connect(lambda: self.start_export_excel_thread())
        self.csv_reader_ui.actioncopyrigth.triggered.connect(lambda: CopyrightDialog().exec())
        self.csv_reader_ui.actionDownload_CSV.triggered.connect(lambda: self.start_export_csv_thread())
        self.csv_reader_ui.actionExcute.triggered.connect(lambda: self.start_query_file_thread())
        self.csv_reader_ui.actionStop.triggered.connect(lambda: self.stop_query())
        self.csv_reader_ui.actionloadToDB.triggered.connect(lambda: self.start_load_to_db_thread())
        self.csv_reader_ui.actionloadToRemoteDB.triggered.connect(lambda: self.open_load_remote_db_dialog())
        self.csv_reader_ui.actionCheckCSV.triggered.connect(lambda: self.check_csv())
        self.csv_reader_ui.actionIngest_To_DB.triggered.connect(lambda: self.open_ingest_to_db_dialog())

        update_process = UpdateProgress(app)
        self.csv_reader_ui.actionCheckUpdate.triggered.connect(lambda: update_process.check_for_updates())

        self.csv_reader_ui.sELECTLineEdit.returnPressed.connect(lambda: self.start_query_file_thread())
        self.csv_reader_ui.wHERELineEdit.returnPressed.connect(lambda: self.start_query_file_thread())
        self.csv_reader_ui.oRDERBYLineEdit.returnPressed.connect(lambda: self.start_query_file_thread())
        self.csv_reader_ui.lIMITLineEdit.returnPressed.connect(lambda: self.start_query_file_thread())
        self.csv_reader_ui.sQLLineEdit.returnPressed.connect(lambda: self.start_query_file_thread())

        self.sub_thread = None
        self.download_thread = None
        self.mutex = threading.Lock()

        self.csv_reader_ui.actionloadToRemoteDB.setEnabled(False)
        self.csv_reader_ui.actionDownload_CSV.setEnabled(False)
        self.csv_reader_ui.actionDownload.setEnabled(False)
        self.csv_reader_ui.actionloadToDB.setEnabled(False)
        self.csv_reader_ui.actionReload.setEnabled(False)
        self.csv_reader_ui.actionExcute.setEnabled(False)
        self.csv_reader_ui.actionCheckCSV.setEnabled(False)

    def check_csv(self):
        def show_log_entry(value, params1, params2):
            dialog.add_log_entry(params1)
            pass

        if self.file_type.lower() == 'csv':
            dialog = LogDialog(self)
            dialog.show()
            signal_emitter = SignalEmitter()
            signal_emitter.result_signal.connect(show_log_entry)
            column_delimiter = self.csv_reader_ui.columnDelmiterComboBox.currentText()
            check_csv_file_thread = threading.Thread(target=check_csv_file,
                                                     args=(self.file_path, signal_emitter, column_delimiter))
            check_csv_file_thread.start()
        else:
            QMessageBox.information(self, "Hit", "Only CSV files can be checked")

    def stop_query(self):
        pass

    def open_load_remote_db_dialog(self):
        LoadToRemoteDBDialog(self).show()

    def open_ingest_to_db_dialog(self):
        IngestToDBDialog(self).show()

    def reload_file(self, file_path):
        if file_path == "":
            return
        reply = QMessageBox.question(self,
                                     "Load",
                                     "Are you sure you want to reload the file?",
                                     QMessageBox.Yes | QMessageBox.No,
                                     QMessageBox.Yes)
        if reply == QMessageBox.Yes:
            self.start_open_file_thread(file_path)

    def start_open_file_thread(self, file_path=None):
        if file_path is None:
            file_dialog = QFileDialog()
            file_path, _ = file_dialog.getOpenFileName(None, "Choose File", "",
                                                       "File (*.csv;*.parquet);;TXT File ("
                                                       "*.txt);;All File (*.*)")
        if file_path == '':
            return
        if self.sub_thread is not None and self.sub_thread.is_alive():
            return

        self.csv_reader_ui.actionloadToRemoteDB.setEnabled(True)
        self.csv_reader_ui.actionDownload_CSV.setEnabled(True)
        self.csv_reader_ui.actionDownload.setEnabled(True)
        self.csv_reader_ui.actionloadToDB.setEnabled(True)
        self.csv_reader_ui.actionReload.setEnabled(True)
        self.csv_reader_ui.actionExcute.setEnabled(True)
        self.csv_reader_ui.actionCheckCSV.setEnabled(True)

        self.file_path = file_path
        self.csv_reader_ui.filePathLineEdit.setText(file_path)
        file_name = os.path.basename(file_path)
        self.table_name = filename_to_table_name(file_name)
        file_size = os.path.getsize(file_path)
        self.csv_reader_ui.loadCountLabel.show()
        self.csv_reader_ui.loadCountLabel.setText(f"Loading...")
        signal_emitter = SignalEmitter()
        signal_emitter.result_signal.connect(self.close_load_window)
        self.open_load_window()
        self.sub_thread = threading.Thread(target=self.select_file, args=(signal_emitter,))
        self.sub_thread.start()

    def start_query_file_thread(self):
        if self.sub_thread is not None and self.sub_thread.is_alive():
            return
        signal_emitter = SignalEmitter()
        signal_emitter.result_signal.connect(self.close_load_window)
        self.open_load_window()
        self.sub_thread = threading.Thread(target=self.search_data, args=(signal_emitter,))
        self.sub_thread.start()

    def start_load_to_db_thread(self):
        reply = QMessageBox.question(self,
                                     "Load to Local DB",
                                     "Are you sure you want to load the file to local DB?",
                                     QMessageBox.Yes | QMessageBox.No,
                                     QMessageBox.Yes)
        if reply == QMessageBox.Yes:
            if self.sub_thread is not None and self.sub_thread.is_alive():
                return
            signal_emitter = SignalEmitter()
            signal_emitter.result_signal.connect(self.close_load_window)
            self.open_load_window()
            self.sub_thread = threading.Thread(target=self.load_to_db, args=(signal_emitter,))
            self.sub_thread.start()

    def start_export_excel_thread(self):
        if self.csv_reader_ui.filePathLineEdit.text() == "":
            return
        if self.download_thread is not None and self.download_thread and self.sub_thread.is_alive():
            return
        self.csv_reader_ui.actionDownload_CSV.setEnabled(False)
        self.csv_reader_ui.actionDownload.setEnabled(False)
        self.csv_reader_ui.actionStop.setVisible(True)
        self.csv_reader_ui.actionExcute.setVisible(False)
        # 定义最大行数常量
        # max_excel_rows = 1048576
        # if self.df is None:
        #     return
        # 检查数据行数是否超过最大行数
        # total_rows = len(self.df)
        # if total_rows > max_excel_rows:
        #     QMessageBox.warning(self, 'Warning', f'Current rows ({total_rows}) over the maximum number of rows in '
        #                                          f'Excel ({max_excel_rows}),Can not download.')
        #     print(f"警告：数据行数超过 Excel 的最大行数 {max_excel_rows}，将截断数据。")
        # else:
        self.csv_reader_ui.progressBar.show()
        self.csv_reader_ui.progressText.setText("Downloading...")
        self.csv_reader_ui.progressText.show()
        signal_emitter = SignalEmitter()
        signal_emitter.result_signal.connect(self.close_download_window)
        # self.open_load_window()
        self.download_thread = threading.Thread(target=self.export_excel, args=(signal_emitter,))
        self.download_thread.start()

    def start_export_csv_thread(self):
        if self.csv_reader_ui.filePathLineEdit.text() == "":
            return
        if self.download_thread is not None and self.download_thread.is_alive() and self.sub_thread.is_alive():
            return
        self.csv_reader_ui.actionDownload_CSV.setEnabled(False)
        self.csv_reader_ui.actionDownload.setEnabled(False)
        self.csv_reader_ui.actionStop.setVisible(True)
        self.csv_reader_ui.actionExcute.setVisible(False)
        self.csv_reader_ui.progressBar.show()
        self.csv_reader_ui.progressText.setText(f"Downloading...")
        self.csv_reader_ui.progressText.show()
        signal_emitter = SignalEmitter()
        signal_emitter.result_signal.connect(self.close_download_window)
        self.download_thread = threading.Thread(target=self.export_csv, args=(signal_emitter,))
        self.download_thread.start()

    def close_load_window(self, value, params1, params2):
        if params1 == "load_count":
            self.csv_reader_ui.loadCountLabel.setText(params2)
            return

        self.csv_reader_ui.actionStop.setVisible(False)
        self.csv_reader_ui.actionExcute.setVisible(True)
        self.csv_reader_ui.actionDownload_CSV.setEnabled(True)
        self.csv_reader_ui.actionDownload.setEnabled(True)
        if params1 != "load_to_db":
            self.csv_reader_ui.sQLLineEdit.setText(params1)
        if value:
            if params1 == "load_to_db":
                QMessageBox.information(self, 'Success', params2)
            self.csv_reader_ui.countLineEdit.setText(params2)
            self.csv_reader_ui.label.hide()
            self.csv_reader_ui.label.hide()
            self.csv_reader_ui.tableView.show()
            self.csv_reader_ui.tableView.setModel(self.model)
            self.csv_reader_ui.loadCountLabel.hide()
        else:
            # QMessageBox.critical(self, 'Error', params1)
            self.csv_reader_ui.textEdit.show()
            self.csv_reader_ui.textEdit.setText(str(params2))
            self.csv_reader_ui.label.hide()
            # self.csv_reader_ui.tableView.show()
            # self.csv_reader_ui.tableView.setModel(self.model)
            self.csv_reader_ui.loadCountLabel.hide()

    def close_download_window(self, value, error_str):
        self.csv_reader_ui.progressBar.hide()
        self.csv_reader_ui.progressText.hide()
        self.csv_reader_ui.actionStop.setVisible(False)
        self.csv_reader_ui.actionDownload_CSV.setEnabled(True)
        self.csv_reader_ui.actionDownload.setEnabled(True)

        self.csv_reader_ui.actionExcute.setVisible(True)
        if value:
            # 显示成功的提示框
            msg_box = QMessageBox()
            msg_box.setIcon(QMessageBox.Information)
            msg_box.setText(
                f"Excel Download Successfully! \nFile Path: {os.path.join(self.download_file_path, self.export_file_name)}")
            msg_box.setWindowTitle("Download Successfully")
            msg_box.setStandardButtons(QMessageBox.Ok)
            msg_box.exec()
        else:
            # 显示错误提示框
            msg_box = QMessageBox()
            msg_box.setIcon(QMessageBox.Critical)
            msg_box.setText(f"Download Failed!\n{error_str}")
            msg_box.setWindowTitle("Download Failed!")
            msg_box.setStandardButtons(QMessageBox.Ok)
            msg_box.exec()

    def load_to_db(self, signal_emitter: SignalEmitter):
        local_conn = duckdb.connect(database='fu_reader.db')
        column_delimiter = self.csv_reader_ui.columnDelmiterComboBox.currentText()
        try:
            if self.file_type.lower() == 'csv':
                load_file_by_duckdb(local_conn, self.file_path, self.table_name, True, column_delimiter)
            else:
                load_file_by_duckdb(local_conn, self.file_path, self.table_name, False, column_delimiter)
            signal_emitter.result_signal.emit(True, 'load_to_db', f'{self.table_name} Load to DB successfully')
        except Exception as e:
            signal_emitter.result_signal.emit(False, 'load_to_db', str(e))
        finally:
            local_conn.close()

    def open_load_window(self):
        print("open load window")
        self.csv_reader_ui.actionStop.setVisible(True)
        self.csv_reader_ui.actionExcute.setVisible(False)
        self.csv_reader_ui.actionDownload_CSV.setEnabled(False)
        self.csv_reader_ui.actionDownload.setEnabled(False)
        self.csv_reader_ui.movie = QMovie(str(self.gif_path))
        self.csv_reader_ui.label.setMovie(self.csv_reader_ui.movie)
        self.csv_reader_ui.movie.start()
        self.csv_reader_ui.tableView.hide()
        self.csv_reader_ui.label.show()
        self.csv_reader_ui.textEdit.hide()

    def on_header_right_click(self, logical_index):
        header_text = self.csv_reader_ui.tableView.horizontalHeader().model().headerData(logical_index, Qt.Horizontal)
        clipboard = QApplication.clipboard()
        clipboard.setText(header_text)

    def export_excel(self, signal_emitter: SignalEmitter):
        current_time = datetime.now().strftime('%Y%m%d%H%M%S')
        self.export_file_name = f"{self.table_name}_{current_time}.xlsx"
        try:
            # 上锁
            # self.mutex.acquire()
            query_statement = self.csv_reader_ui.sQLLineEdit.text()
            if self.file_type.lower() == 'csv':
                query_statement = query_statement.replace(self.table_name, self.file_path)
            header, data = query_data_by_duckdb(query_statement, self.conn.cursor())
            wb = Workbook()
            ws = wb.active
            ws.append(header)
            for row in data:
                ws.append(row)
            wb.save(os.path.join(self.download_file_path, self.export_file_name))
            signal_emitter.result_signal.emit(True, None, None)
        except Exception as e:
            print(f"导出Excel文件时发生错误：{e}")
            signal_emitter.result_signal.emit(False, str(e), None)

    def export_csv(self, signal_emitter: SignalEmitter):
        current_time = datetime.now().strftime('%Y%m%d%H%M%S')
        self.export_file_name = f"{self.table_name}_{current_time}.csv"
        try:
            # 上锁
            # self.mutex.acquire()
            query_statement = self.csv_reader_ui.sQLLineEdit.text()
            if self.file_type.lower() == 'csv':
                export_statement = f"COPY ({query_statement}) TO '{self.download_file_path}/{self.export_file_name}' (HEADER 1, DELIMITER ',')"
            else:
                query = query_statement.replace(self.table_name, self.file_path)
                export_statement = f"COPY ({query}) TO '{self.download_file_path}/{self.export_file_name}' (HEADER 1, DELIMITER ',')"
            self.conn.execute(export_statement)
            # 释放锁
            # self.mutex.release()
            signal_emitter.result_signal.emit(True, None, None)
        except Exception as e:
            print(f"导出CSV文件时发生错误：{e}")
            signal_emitter.result_signal.emit(False, str(e), None)

    def select_file(self, signal_emitter: SignalEmitter):
        try:
            if self.file_path:
                self.csv_reader_ui.lIMITLineEdit.setText('')
                self.csv_reader_ui.sELECTLineEdit.setText('')
                self.csv_reader_ui.wHERELineEdit.setText('')
                self.csv_reader_ui.oRDERBYLineEdit.setText('')
                file_name = os.path.basename(self.file_path)
                self.file_type = file_name.split('.')[-1]
                self.table_name = filename_to_table_name(file_name)
                if self.file_type.lower() == 'csv':
                    row_delimiter = self.csv_reader_ui.rowDelmiterComboBox.currentText()
                    column_delimiter = self.csv_reader_ui.columnDelmiterComboBox.currentText()
                    load_file_by_duckdb(self.conn.cursor(), self.file_path, self.table_name, True, column_delimiter)
                self.query_data(signal_emitter)
        except Exception as e:
            print(e)
            # self.csv_reader_ui.label.setText(str(e))
            signal_emitter.result_signal.emit(False, None, str(e))

    def search_data(self, signal_emitter: SignalEmitter):
        global query_sql
        try:
            if not self.file_path:
                signal_emitter.result_signal.emit(False, None, "Please Open File...")
                # self.csv_reader_ui.label.setText('Please Open File...')
                return

            file_name = os.path.basename(self.file_path)
            self.table_name = filename_to_table_name(file_name)
            query_sql = self.csv_reader_ui.sQLLineEdit.text()
            self.query_data(signal_emitter, query_sql)
        except Exception as e:
            signal_emitter.result_signal.emit(False, query_sql, str(e))
            print(e)

    def query_data(self, signal_emitter: SignalEmitter, query=None):
        # file_path = self.csv_reader_ui.filePathLineEdit.text()

        name = self.table_name if self.file_type.lower() == 'csv' else self.file_path
        # 模拟数据加载过程
        query_sql = f"SELECT * FROM '{name}' LIMIT 1000" if query is None else query

        if self.file_type.lower() != 'csv':
            query_sql = query_sql.replace(self.table_name, self.file_path)

        if query_sql.lower().find("limit") == -1:
            query_sql += " LIMIT 1000"
        try:

            print("start query_data")

            count_query = f"""select count(*) {query_sql[query_sql.upper().find("FROM"):query_sql.upper().find("LIMIT")]}"""
            count_query = count_query[:count_query.upper().find("ORDER BY")]
            # _, count_query = convert_csv_query(query_sql)
            print(f"count_query {count_query}")
            _, count = query_data_by_duckdb(count_query, self.conn.cursor())

            count_str = f"{count[0][0]:,}"
            # count_str = 100
            # self.csv_reader_ui.countLineEdit.setText(count_str)

            header, data = query_data_by_duckdb(query_sql, self.conn.cursor())

            print("end sql")

            # self.df = pd.DataFrame(data, columns=header)
            # 创建一个 QStandardItemModel 实例
            self.model = QStandardItemModel()
            # 设置表头
            self.model.setHorizontalHeaderLabels(header)
            # 添加数据
            # header_table = self.csv_reader_ui.tableView.horizontalHeader()

            for row, values in enumerate(data):
                for column, value in enumerate(values):
                    if isinstance(value, (float, int, Decimal, datetime, date)):
                        if row == 0:
                            # 设置表头对齐方式
                            pass
                            # column_selector = f"QHeaderView::section:horizontal:{{column-number: {column};}}"
                            # style_sheet = f"{column_selector} text-align: left;"
                            # style_sheet = "QHeaderView::section:horizontal: {text-align: left;}"
                            # header_table.setStyleSheet(style_sheet)
                        if isinstance(value, float):
                            value = f"{value:.2f}"
                        elif isinstance(value, Decimal):
                            value = format(value, ',.2f')
                        elif isinstance(value, date):
                            value = value.strftime('%Y-%m-%d')
                        elif isinstance(value, datetime):
                            value = value.strftime('%Y-%m-%d %H:%M:%S')
                        item = QStandardItem(str(value))
                        item.setTextAlignment(Qt.AlignRight)
                    else:
                        item = QStandardItem(value)
                    self.model.setItem(row, column, item)

                # 将模型绑定到 QTableView
            # self.csv_reader_ui.tableView.setModel(self.model)
            if self.file_type.lower() != 'csv':
                parquet_sql = query_sql.replace(self.file_path, self.table_name)
                signal_emitter.result_signal.emit(True, parquet_sql, count_str)
            else:
                signal_emitter.result_signal.emit(True, query_sql, count_str)
        except Exception as e:
            raise e


class CustomHeaderView(QHeaderView):
    def __init__(self, orientation, parent=None, main_window=None):
        super().__init__(orientation, parent)
        self.main_window = main_window

    def mousePressEvent(self, event):
        if event.button() == Qt.RightButton:
            logical_index = self.logicalIndexAt(event.pos())
            if logical_index != -1:
                menu = QMenu(self)

                # 添加菜单项
                actionChooseColumnName = QAction("CHOOSE COLUMN NAME", self)
                actionDesc = QAction("ORDER BY DESC", self)
                actionAsc = QAction("ORDER BY ASC", self)
                header_text = self.model().headerData(logical_index, self.orientation())
                actionDesc.triggered.connect(
                    lambda: on_action_order_by_triggered(self.main_window, header_text, "DESC"))
                actionAsc.triggered.connect(lambda: on_action_order_by_triggered(self.main_window, header_text, "ASC"))
                actionChooseColumnName.triggered.connect(
                    lambda: on_action_choose_column_triggered(self.main_window, header_text))

                menu.addAction(actionDesc)
                menu.addAction(actionAsc)
                menu.addAction(actionChooseColumnName)

                # 显示菜单
                menu.exec(event.globalPos())

        if event.button() == Qt.LeftButton:
            logical_index = self.logicalIndexAt(event.pos())
            if logical_index != -1:
                header_text = self.model().headerData(logical_index, self.orientation())
                clipboard = QApplication.clipboard()
                clipboard.setText(f"\"{header_text}\"")

        super().mousePressEvent(event)